package org.emmet.db4j.core.util;

import org.emmet.db4j.core.bean.Table;
import org.emmet.db4j.core.bean.TableColumn;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;


/**
 * 数据库辅助类
 */
public class DBUtil {


    private static String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/cdaecs?useSSL=false&remarks=true&useInformationSchema=true";
    private static String USER = "root";
    private static String PASSWORD = "";
    private static Connection conn = null;
    private static String OUTPUT_PATH = "/home/emmet/projects/DB4J/db4j-core/src/main/java/";
    private static String OUTPUT_PACKAGE = "org.emmet.db4j.core.entity";
    public static final String KEY_SUFFIX= "Fix";
    public static final String SYSTEM_KEYWORD = ",private,protected,public" +
            "abstract,class,extends,final,implements,interface,native,new" +
            "static,strictfp,synchronized,transient,volatile" +
            "break,continue,return,do,while,if,else,for,instanceof,switch" +
            "case,default" +
            "try,catch,throw,throws" +
            "import,package" +
            "boolean,byte,char,double,float,int,long,short,null,true,false" +
            "super,this,void" +
            "goto,const,";
    public static Map<String, List<TableColumn>> tableColumnMap = new Hashtable<String, List<TableColumn>>();

    //初始化数据库
    static {
        try {
            Class.forName(DRIVER_NAME);
        } catch (ClassNotFoundException e) {
            System.out.println("未找到驱动类[" + DRIVER_NAME + "],请检查是否引入对应Jar包");
            e.printStackTrace();
        }

    }

    public static Connection getConnection() {
        if (conn == null) {

            try {
                conn = DriverManager.getConnection(URL, USER, PASSWORD);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;

    }

    public static List<Table> getTables() {
        Connection connection = getConnection();
        List<Table> tablesNames = new ArrayList<Table>();
        try {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            while (tables.next()) {
                String db = tables.getString("TABLE_CAT");
                if (!db.equalsIgnoreCase(connection.getCatalog())) {
                    continue;
                }

                String tableName = tables.getString("TABLE_NAME");  //表名
                String tableType = tables.getString("TABLE_TYPE");  //表类型
                String remarks = tables.getString("REMARKS");       //表备注
                tablesNames.add(new Table(tableName, remarks));
            }
            ResultSet columns = metaData.getColumns(connection.getCatalog(), null, "%", "%");
            while (columns.next()) {
                String tableName = columns.getString("TABLE_NAME");  //表名
                String columnName = columns.getString("COLUMN_NAME");
                String columnType = columns.getString("TYPE_NAME");
                int dataSize = columns.getInt("COLUMN_SIZE");
                int digits = columns.getInt("DECIMAL_DIGITS");
                int nullable = columns.getInt("NULLABLE");
                String remarks = columns.getString("REMARKS");       //表备注
//                System.out.println(tableName + "->" + columnName + " " + columnType + " " + dataSize + " " + digits + " " +
//                        nullable);
                TableColumn tableColumn = new TableColumn(columnName, columnType, nullable == 1, remarks);
                addColumnForTableColumnMap(tableName, tableColumn);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tablesNames;
    }

    private static void addColumnForTableColumnMap(String tableName, TableColumn tableColumn) {
        //如果key不存在，初始化
        if (!tableColumnMap.containsKey(tableName)) {
            tableColumnMap.put(tableName, new ArrayList<TableColumn>());
        }
        tableColumnMap.get(tableName).add(tableColumn);

    }

    public static void createEntityFile(Table table) {
        String tableName = table.getTableName();
        if (StringUtil.isEmpty(tableName)) {
            return;
        }
        String javaFileName = StringUtil.toUpperCaseFirstOne(tableName);
        String tempOutputPackage = OUTPUT_PACKAGE;
        if (OUTPUT_PACKAGE.contains(".")) {
            tempOutputPackage = OUTPUT_PACKAGE.replaceAll("\\.", "/");
        }
        String tempOutputPath = OUTPUT_PATH;
        if (!OUTPUT_PATH.endsWith("/")) {
            tempOutputPath = OUTPUT_PATH + "/";
        }
        if (!tempOutputPackage.endsWith("/")) {
            tempOutputPackage = tempOutputPackage + "/";
        }
        String pathname = tempOutputPath + tempOutputPackage;

        File path = new File(pathname);
        if (!path.exists()) {
            path.mkdirs();
        }
        String javaFile = pathname + javaFileName + ".java";
        String content = pressTableToJava(table);
        try {
            FileWriter fileWriter = new FileWriter(javaFile);
            PrintWriter printWriter = new PrintWriter(fileWriter);
            printWriter.print(content);
            printWriter.flush();
            printWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String pressTableToJava(Table table) {
        final String LINE = "\r\n";
        final String TAB_STR = "\t";
        final String BRACES_LEFT = "{";
        final String BRACES_RIGHT = "}";
        final String END_SYMBOL = ";";
        StringBuilder content = new StringBuilder();
        content.append("package ").append(OUTPUT_PACKAGE).append(END_SYMBOL).append(LINE);
        content.append("import java.util.Date").append(END_SYMBOL);
        //类注释
        content.append(LINE)
                .append("/**")
                .append(LINE)
                .append("/*")
                .append(table.getRemark())
                .append(LINE)
                .append("*/")
                .append(LINE);
        //类
        content.append("public class ")
                .append(StringUtil.toUpperCaseFirstOne(table.getTableName()))
                .append(BRACES_LEFT);
        //属性
        List<TableColumn> tableColumnList = tableColumnMap.get(table.getTableName());
        StringBuilder methodString = new StringBuilder(LINE);
        for (TableColumn tableColumn : tableColumnList) {
            String remkes = "";
            if (StringUtil.isNotEmpty(tableColumn.getRemarks())) {
                remkes = LINE + TAB_STR + "//" + tableColumn.getRemarks();
            }
            String type = sqlType2JavaType(tableColumn.getTypeFix());
            content.append(remkes).append(LINE).append(TAB_STR).append("private ")
                    .append(type)
                    .append(" ")
                    .append(tableColumn.getColumnName())
                    .append(END_SYMBOL);
            methodString.append(LINE).append(TAB_STR)
                    .append("public ")
                    .append(type)
                    .append(" get")
                    .append(StringUtil.toUpperCaseFirstOne(tableColumn.getColumnName()))
                    .append("() ")
                    .append(BRACES_LEFT)
                    .append(LINE)
                    .append(TAB_STR).append(TAB_STR)
                    .append("return this.")
                    .append(tableColumn.getColumnName())
                    .append(END_SYMBOL)
                    .append(LINE)
                    .append(TAB_STR)
                    .append(BRACES_RIGHT)
                    .append(LINE);
            methodString.append(TAB_STR)
                    .append("public void")
                    .append(" set")
                    .append(StringUtil.toUpperCaseFirstOne(tableColumn.getColumnName()))
                    .append("(").append(type).append(" ").append(tableColumn.getColumnName()).append(") ")
                    .append(BRACES_LEFT)
                    .append(LINE)
                    .append(TAB_STR).append(TAB_STR)
                    .append("this.")
                    .append(tableColumn.getColumnName()).append(" = ").append(tableColumn.getColumnName())
                    .append(END_SYMBOL)
                    .append(LINE)
                    .append(TAB_STR)
                    .append(BRACES_RIGHT)
                    .append(LINE);
            //public void setId(int id) {\n        this.id = id;\n    }";
        }
        //方法
        content.append(LINE).append(methodString);

        //类结束
        content.append(LINE).append(BRACES_RIGHT);
        return content.toString();
    }

    /**
     * 功能：获得列的数据类型
     *
     * @param sqlType
     * @return
     */
    private static String sqlType2JavaType(String sqlType) {

        if (sqlType.equalsIgnoreCase("bit")) {
            return "boolean";
        } else if (sqlType.equalsIgnoreCase("tinyint")) {
            return "byte";
        } else if (sqlType.equalsIgnoreCase("smallint")) {
            return "short";
        } else if (sqlType.equalsIgnoreCase("int")) {
            return "int";
        } else if (sqlType.equalsIgnoreCase("bigint")) {
            return "long";
        } else if (sqlType.equalsIgnoreCase("float")) {
            return "float";
        } else if (sqlType.equalsIgnoreCase("decimal")
                || sqlType.equalsIgnoreCase("numeric")
                || sqlType.equalsIgnoreCase("real")
                || sqlType.equalsIgnoreCase("money")
                || sqlType.equalsIgnoreCase("smallmoney")) {
            return "double";
        } else if (sqlType.equalsIgnoreCase("varchar")
                || sqlType.equalsIgnoreCase("char")
                || sqlType.equalsIgnoreCase("nvarchar")
                || sqlType.equalsIgnoreCase("nchar")
                || sqlType.equalsIgnoreCase("text")
                || sqlType.equalsIgnoreCase("enum")
                ) {
            return "String";
        } else if (sqlType.equalsIgnoreCase("datetime") || sqlType.equalsIgnoreCase("timestamp")) {
            return "Date";
        } else if (sqlType.equalsIgnoreCase("image")) {
            return "Blod";
        } else if (sqlType.equalsIgnoreCase("blob")) {
            return "byte[]";
        } else {
            System.out.println(sqlType);
        }

        return null;
    }


    public static void close() {

    }
}
